const connection = require('../app/database');
const connections = require("../app/database");
const sqlFarm = ` 
                  SELECT            
                  m.content content, m.id id ,m.createAt createAt,m.updateAt updateAt,
                  JSON_OBJECT('id',u.id , 'name',u.name) users
                  FROM moment m
                  LEFT JOIN users u 
                  ON m.user_id = u.id `

class MomentService {
  //数据库是异步操作，所以需要使用promise  await   async
  async create(userId, content) {
    const sql = `INSERT INTO moment (content , user_id)VALUES(?,?);`;
    const [result] = await connection.execute(sql, [content, userId]);  //result is an array
    return result;
  }

  /**
   * @param momentId
   * @returns {Promise<*>}
   */
  async detail(momentId) {
    try {
      const sql = `SELECT 
m.content ,m.id ,m.createAt ,m.updateAt,
JSON_OBJECT('id',u.id,'name',u.name) user,
(SELECT COUNT(*) FROM comment c WHERE c.moment_id =m.id ) commentCount,
(SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id =m.id ) labelCount,
JSON_ARRAYAGG(JSON_OBJECT('id',l.id,'name',l.name) )labels,
(SELECT JSON_ARRAYAGG(CONCAT('http://localhost:3000/moment/images/',f.filename)) 
FROM file f WHERE f.moment_id = m.id ) images
FROM moment m 
LEFT JOIN users u ON m.user_id = u.id
LEFT JOIN moment_label ml ON ml.moment_id = m.id
LEFT JOIN label l on ml.label_id = l.id 
WHERE m.id = ?;
`;
      const [result] = await connection.execute(sql, [momentId]);
      return result;
    } catch (e) {
      console.log(e)
    }
  }

  /**
   *
   * @param offset
   * @param size
   * @returns {Promise<*>}
   */
  async getMomentList(offset, size) {
    const sql = `SELECT 
                 m.content ,m.id ,m.createAt ,m.updateAt,
                 JSON_OBJECT('id',u.id,'name',u.\`name\`) user,
                 (SELECT COUNT(*) FROM comment c WHERE c.moment_id =m.id ) commentCount,
                  (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id =m.id ) labelCount,
                  (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:3000/moment/images/',f.filename)) FROM file f WHERE f.moment_id = m.id ) images
                 FROM moment m 
                 LEFT JOIN users u
                 ON m.user_id = u.id
                 LIMIT 0 ,10;`;
    const [result] = await connection.execute(sql, [offset, size]);
    return result;
  }

  /**
   *
   * @param momentId
   * @param content
   * @returns {Promise<*>}
   */
  async update(momentId, content) {
    try {
      const sql = `UPDATE moment SET content = ? WHERE id = ?;`;
      const [result] = await connection.execute(sql, [content, momentId]);
      console.log(result, 'result');
      return result;
    } catch (e) {
      console.log(e);
    }
  }

  async deleteMoment(momentId) {
    try {
      const sql = `DELETE FROM moment WHERE id = ?;`;
      const [result] = await connection.execute(sql, [momentId]);
      console.log(result, 'result');
      return result;
    } catch (e) {
      console.log(e);
    }
  }

  // async createLabel
  //查询动态中已有的标签
  async hasLabel(momentId, labelId) {
    try {
      console.log(momentId, labelId);
      const statement = `SELECT * FROM moment_label WHERE moment_id = ? AND label_id = ?;`
      const [result] = await connections.execute(statement, [momentId, labelId]);
      return result[0];
    } catch (e) {
      console.log(e);
    }
  }

  async createLabelByMoment(labelId, momentId) {
    try {
      const statement = `INSERT INTO moment_label (moment_id, label_id) VALUES (?,?);`
      const [result] = await connections.execute(statement, [momentId, labelId]);
      return result;
    } catch (e) {
      console.log(e);
    }
  }
}

module.exports = new MomentService();